import sqlite3
from cons import DATABASE_NAME


def db_init():
    # 新建连接db数据库，创建表
    conn = sqlite3.connect(DATABASE_NAME)
    print("数据库创建成功!")
    try:
        conn.execute("""
        CREATE TABLE USERS(
        user_id INTEGER PRIMARY KEY NOT NULL,
        user_name TEXT NOT NULL, 
        password TEXT NOT NULL)
        """)
    except:
        print("USERS表已经创建")
    else:
        print("USERS表创建成功！")

    try:
        conn.execute("""
        CREATE TABLE WORDS(
        words_id INTEGER PRIMARY KEY NOT NULL,
        words_name TEXT NOT NULL, 
        words_mean TEXT)
        """)
    except:
        print("WORDS表已经创建")
    else:
        print("WORDS表创建成功！")

    try:
        conn.execute("""
        CREATE TABLE WRONG_WORDS(
        words_id INTEGER,
        user_id INTEGER,
        wrong_status INTEGER,
        FOREIGN KEY(words_id) REFERENCES WORDS(words_id),
        FOREIGN KEY(user_id) REFERENCES USERS(user_id)) 
        """)
    except:
        print("WRONG_WORDS表已经创建")
    else:
        print("WRONG_WORDS表创建成功！")

    try:
        conn.execute("""
        CREATE TABLE USER_WORDS(
        user_id INTEGER,
        words_id INTEGER,
        words_status INTEGER DEFAULT 0,
        FOREIGN KEY(words_id) REFERENCES WORDS(words_id),
        FOREIGN KEY(user_id) REFERENCES USERS(user_id)) 
        """)
    except:
        print("USER_WORDS表已经创建")
    else:
        print("USER_WORDS表创建成功！")

    try:
        conn.execute("""
        CREATE TABLE USER_NOTE(
        note_id INTEGER PRIMARY KEY NOT NULL,
        user_id INTEGER,
        note TEXT ,
        FOREIGN KEY(user_id) REFERENCES USERS(user_id)) 
        """)
    except:
        print("USER_NOTE表已经创建")
    else:
        print("USER_NOTE表创建成功！")

        # # 向表中写入数据
        # conn.execute("INSERT INTO WORDS (words_name,words_mean) VALUES ('你好', 'hello')");
        # conn.execute("INSERT INTO WORDS (words_name,words_mean) VALUES ('再见', 'good by')");
        # conn.execute("INSERT INTO WRONG_WORDS (words_id,wrong_status) VALUES (1, 0)");
        #
        # conn.commit()
        # print("记录插入成功!")
        # conn.close()
        #
        # # 获取数据库，读取数据并按格式打印
        # conn = sqlite3.connect("YiCiZhan.db")
        #
        # cursor1 = conn.execute("SELECT * FROM WORDS")
        # cursor2 = conn.execute("SELECT * FROM WRONG_WORDS")
        # for row in cursor1:
        #     print("{}\t{}\t{}".format(row[0], row[1], row[2]))
        #
        # for row in cursor2:
        #     print("{}\t{}".format(row[0], row[1]))
        # conn.close()


if __name__ == '__main__':
    db_init()
